import type { KnowledgeDetail } from '@/types/knowledge';

/**
 * 表连接与关联知识点数据
 */
export const joinKnowledge: KnowledgeDetail[] = [
	{
		id: 1,
		title: 'INNER JOIN内连接',
		description: 'INNER JOIN返回两个表中匹配的记录，只显示同时存在于两个表中的数据。',
		icon: '🔗',
		categoryId: 'sql-join',
		categoryName: '表连接与关联',
		categoryIcon: '🔗',
		difficulty: '中级',
		color: 'linear-gradient(135deg, #667eea 0%, #764ba2 100%)',
		headerBg: 'linear-gradient(135deg, #2c3e50 0%, #34495e 100%)',
		tagBg: 'linear-gradient(135deg, #3498db 0%, #2980b9 100%)',
		tagColor: '#ffffff',
		difficultyBg: 'linear-gradient(135deg, #f39c12 0%, #e67e22 100%)',
		keyPoints: [
			{
				point: '基本语法：SELECT ... FROM table1 INNER JOIN table2 ON table1.column = table2.column。',
				code: `-- INNER JOIN：只返回匹配的记录
SELECT u.name, o.order_date, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 等价写法（旧式语法）
SELECT u.name, o.order_date, o.total_amount
FROM users u, orders o
WHERE u.id = o.user_id;

-- 多表内连接
SELECT u.name, o.order_date, oi.product_name, oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id;`
			},
			{
				point: 'INNER JOIN的特点：只返回两个表中都有匹配的记录，不匹配的记录会被过滤掉。',
				code: `-- 只返回有订单的用户
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 没有订单的用户不会出现在结果中
-- 如果需要包含所有用户，应该使用LEFT JOIN`
			},
			{
				point: '使用别名简化：表别名可以简化SQL语句，提高可读性。',
				code: `-- 使用别名
SELECT u.name, u.email, o.total_amount
FROM users AS u
INNER JOIN orders AS o ON u.id = o.user_id;

-- AS关键字可以省略
SELECT u.name, u.email, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;`
			},
			{
				point: '复合连接条件：可以在ON子句中使用多个条件。',
				code: `-- 多条件连接
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id 
    AND o.status = 'completed'
    AND o.order_date >= '2024-01-01';

-- 注意：连接条件应该在ON子句中，过滤条件应该在WHERE子句中`
			},
			{
				point: 'INNER JOIN的性能：通常比LEFT JOIN快，但需要确保连接条件上有索引。',
				code: `-- 性能优化：确保连接列上有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 连接查询
SELECT u.name, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';`
			}
		]
	},
	{
		id: 2,
		title: 'LEFT JOIN和RIGHT JOIN',
		description: 'LEFT JOIN返回左表的所有记录和右表的匹配记录，RIGHT JOIN返回右表的所有记录和左表的匹配记录。',
		icon: '⬅️',
		categoryId: 'sql-join',
		categoryName: '表连接与关联',
		categoryIcon: '🔗',
		difficulty: '中级',
		color: 'linear-gradient(135deg, #667eea 0%, #764ba2 100%)',
		headerBg: 'linear-gradient(135deg, #2c3e50 0%, #34495e 100%)',
		tagBg: 'linear-gradient(135deg, #3498db 0%, #2980b9 100%)',
		tagColor: '#ffffff',
		difficultyBg: 'linear-gradient(135deg, #f39c12 0%, #e67e22 100%)',
		keyPoints: [
			{
				point: 'LEFT JOIN：返回左表的所有记录，右表没有匹配时显示NULL。',
				code: `-- LEFT JOIN：返回所有用户，即使没有订单
SELECT u.name, u.email, o.order_date, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 查找没有订单的用户
SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;`
			},
			{
				point: 'RIGHT JOIN：返回右表的所有记录，左表没有匹配时显示NULL。',
				code: `-- RIGHT JOIN：返回所有订单，即使没有对应的用户
SELECT u.name, o.order_date, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 注意：RIGHT JOIN可以通过交换表顺序用LEFT JOIN实现
-- 通常更推荐使用LEFT JOIN，因为更直观`
			},
			{
				point: 'LEFT JOIN的常见用法：查找缺失数据、统计包含零值等。',
				code: `-- 统计每个用户的订单数（包括没有订单的用户）
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 查找没有购买过某个产品的用户
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
    AND o.product_id = 123
WHERE o.id IS NULL;`
			},
			{
				point: '多表LEFT JOIN：可以连接多个表，每个连接都是独立的。',
				code: `-- 多表LEFT JOIN
SELECT 
    u.name,
    p.profile_data,
    o.order_date,
    oi.product_name
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE u.status = 'active';`
			},
			{
				point: 'LEFT JOIN vs INNER JOIN：根据业务需求选择合适的连接方式。',
				code: `-- INNER JOIN：只要有订单的用户
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- LEFT JOIN：所有用户（包括没有订单的）
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 选择原则：
-- - 需要包含所有左表记录：LEFT JOIN
-- - 只需要匹配记录：INNER JOIN`
			}
		]
	},
	{
		id: 3,
		title: '自连接（Self Join）',
		description: '自连接是将表与自身进行连接，用于查询表中的层次关系或比较同一表中的不同记录。',
		icon: '🔄',
		categoryId: 'sql-join',
		categoryName: '表连接与关联',
		categoryIcon: '🔗',
		difficulty: '中级',
		color: 'linear-gradient(135deg, #667eea 0%, #764ba2 100%)',
		headerBg: 'linear-gradient(135deg, #2c3e50 0%, #34495e 100%)',
		tagBg: 'linear-gradient(135deg, #3498db 0%, #2980b9 100%)',
		tagColor: '#ffffff',
		difficultyBg: 'linear-gradient(135deg, #f39c12 0%, #e67e22 100%)',
		keyPoints: [
			{
				point: '自连接的基本用法：使用不同的表别名连接同一个表。',
				code: `-- 查找员工及其经理
SELECT 
    e.name AS employee_name,
    m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 查找同一城市的用户对
SELECT 
    u1.name AS user1,
    u2.name AS user2,
    u1.city
FROM users u1
INNER JOIN users u2 ON u1.city = u2.city
WHERE u1.id < u2.id;  -- 避免重复和自匹配`
			},
			{
				point: '层次关系查询：使用自连接查询树形结构数据。',
				code: `-- 查找员工的管理层级（经理的经理）
SELECT 
    e.name AS employee,
    m1.name AS manager,
    m2.name AS senior_manager
FROM employees e
LEFT JOIN employees m1 ON e.manager_id = m1.id
LEFT JOIN employees m2 ON m1.manager_id = m2.id;

-- 查找所有下属
SELECT m.name AS manager, COUNT(e.id) AS subordinates_count
FROM employees m
LEFT JOIN employees e ON m.id = e.manager_id
GROUP BY m.id, m.name;`
			},
			{
				point: '比较同一表中的记录：使用自连接比较不同记录。',
				code: `-- 查找薪资相同的员工
SELECT 
    e1.name AS employee1,
    e2.name AS employee2,
    e1.salary
FROM employees e1
INNER JOIN employees e2 ON e1.salary = e2.salary
WHERE e1.id < e2.id;  -- 避免重复

-- 查找比某员工薪资高的员工
SELECT 
    e1.name AS employee,
    e1.salary,
    e2.name AS higher_paid_employee,
    e2.salary AS higher_salary
FROM employees e1
INNER JOIN employees e2 ON e2.salary > e1.salary
WHERE e1.name = 'John';`
			},
			{
				point: '递归查询（CTE）：某些数据库支持递归CTE查询层次结构。',
				code: `-- PostgreSQL/MySQL 8.0+ 递归CTE
WITH RECURSIVE employee_hierarchy AS (
    -- 基础查询：顶级经理
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询：下属
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;`
			},
			{
				point: '自连接的性能：注意避免笛卡尔积，确保连接条件正确。',
				code: `-- ❌ 错误：没有连接条件，产生笛卡尔积
SELECT e1.name, e2.name
FROM employees e1, employees e2;
-- 结果：n * n 条记录（n为员工数）

-- ✅ 正确：有连接条件
SELECT e1.name, e2.name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.id;

-- 性能提示：确保连接列上有索引`
			}
		]
	}
];

